

Create VIEW [dbo].[VTotalSystemReport]
AS

select distinct
-- search criteria fields
Case when ProjectStages.Stage = 1 then 0 else 1 end as CustomerType,
EducationalLevels.EducationalLevelID,
Projects.ProjectID,
ProjectStages.Stage,
MilitaryStatuses.MilitaryStatusID,
DATEDIFF(YEAR,Customers.Birthdate,GETDATE()) as CustomerAge,
customers.CurrentJob,
LoanSocialResearch.FamilyNumbers,
LoanSocialResearch.IncomeAmount,
CustomerLoans.LoanTypeID,
CustomerLoans.IsLoanProjectExist as LoanStatus,
CustomerLoans.LegalFormID,
CustomerLoans.LoanPlaceTypeID,
CurrentwfstepInstances.StepID as LoanCurrentStep,
LoanExecution.LoanAmount as ExecutionAmount,
LoanExecution.ExecutionDate,
LastLoanExecutionInstallments.InstallmentDate as LastInstallmentDate,
CustomerLoans.LoanCreatedDate,
wfActions.ActionTypeID,
wfStepInstances.Comments,
wfSteps.StepID,
Board.BoardDate,
Board.BoardNumber,
BoardLoans.StatusNotes as BoardDecision,
Committees.CommitteeID,
CustomerLoans.ResponsibleID,
SocialStatuses.SocialStatusID,
-- displayed fields
CustomerLoans.LoanNumber,
customers.CustomerName,
Customers.SSN,
CustomerLoans.LoanName,
CustomerLoans.LoanAmount,
CustomerLoans.LoanPaymentPeriod,
ISNULL(LoanTotalInstallmentAmount.TotalInstallmentAmount,0) AS LoanPaidAmount, 
(LoanExecution.LoanAmount - ISNULL(LoanTotalInstallmentAmount.TotalInstallmentAmount,0)) as DueAmount,
LoanPaymentMethod.LoanPaymentMethodName
from CustomerLoans 
inner join ProjectStages on CustomerLoans.ProjectStageID = ProjectStages.ProjectStageID
inner join Customers on CustomerLoans.CustomerID = Customers.CustomerID
inner join EducationalLevels on Customers.EducationalLevelID = EducationalLevels.EducationalLevelID
inner join Projects on Projects.ProjectID = ProjectStages.ProjectID
inner join MilitaryStatuses on Customers.MilitaryStatusID = MilitaryStatuses.MilitaryStatusID
inner join LoanSocialResearch on CustomerLoans.LoanID = LoanSocialResearch.LoanID  
inner join LoanType on CustomerLoans.LoanTypeID = LoanType.LoanTypeID
inner join LoanLegalForms on CustomerLoans.LegalFormID = LoanLegalForms.LegalFormID
inner join LoanPlaceTypes on CustomerLoans.LoanPlaceTypeID = LoanPlaceTypes.LoanPlaceID
left outer join wfProcessInstances on CustomerLoans.LoanID = wfProcessInstances.EntityID
left outer join (select ProcessInstanceID,StepID,max(creationdate) as CreationDate
					from wfstepInstances group by ProcessInstanceID,StepID) as CurrentwfstepInstances
			on wfProcessInstances.ProcessInstanceID =  CurrentwfstepInstances.ProcessInstanceID
left outer join wfstepInstances on CurrentwfstepInstances.ProcessInstanceID = wfstepInstances.ProcessInstanceID
left outer join wfSteps on wfstepInstances.StepID = wfSteps.StepID
inner join LoanExecution on LoanExecution.LoanID = CustomerLoans.LoanID
left outer join (select LoanExecutionID,MAX(InstallmentDate) as InstallmentDate 
				from LoanExecutionInstallments
				group by LoanExecutionID) as LastLoanExecutionInstallments
		 on LastLoanExecutionInstallments.LoanExecutionID = LoanExecution.LoanExecutionID
inner join LoanExecutionInstallments on LoanExecutionInstallments.LoanExecutionID = LoanExecution.LoanExecutionID
left outer join (select LoanExecutionInstallmentID,SUM(PaymentAmount) as TotalInstallmentAmount 
				 from LoanInstallmentPayments
				 group by LoanExecutionInstallmentID) as LoanTotalInstallmentAmount
		on LoanTotalInstallmentAmount.LoanExecutionInstallmentID = LoanExecutionInstallments.LoanExecutionInstallmentID
left outer join wfActions on wfSteps.StepID = wfActions.StepID 
inner join Committees on CustomerLoans.CommitteeID = Committees.CommitteeID
inner join Board on Committees.CommitteeID = Board.CommitteeID
inner join BoardLoans on Board.BoardID = BoardLoans.BoardID
inner join LoanPaymentMethod on CustomerLoans.LoanPaymentMethodID = LoanPaymentMethod.LoanPaymentMethodID
inner join SocialStatuses on Customers.SocialStatusID = SocialStatuses.SocialStatusID




GO


